#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   sku2_inverse_level_reverse.py
@Contact :   pengwei.sun@aihuishou.com
@License :   (C)Copyright aihuishou
重点处理c端sku等级倒挂逻辑：

@Modify Time      @Author       @Version    @Desciption
------------      -----------   --------    -----------
2021-06-22 17:09   pengwei.sun      1.0         None
'''
import os
import sys
sys.path.append(os.getcwd())
from src.utils.db_processor import mysql_prediction_processor
import pandas as pd
from src.utils.config import logger
from src.mobile.levelrate.reverse.sku2_skulevel_period_price import get_period_price_fun
from src.mobile.levelrate.reverse.function_utils import save_pickle_data,load_pickle_data,check_conflict_file
import pickle
import numpy as np
from src.utils.util import  get_today, format_date_string
from src.mobile.levelrate.reverse.sku2_common_variable import  FILE_DIR
import concurrent.futures
from multiprocessing import Pool
import time
# AND  price.product_brand_name in ('华为','小米')  and price.product_sku_key in (2670310,283187)
query_sql="""
        select distinct price.date,
        price.product_sku_key+0 as product_sku_key,price.product_sku_name, 
        price.product_level_key+0 as product_level_key,
        price.product_level_name, price.product_key, price.product_name, price.product_category_id, price.product_category_name, price.product_brand_id,price.product_brand_name, 
        price.predict_origin, price.forecast_reference_price,price.is_new_product,price.POLY_pred_price,price.rank,price.price_3,price.price_2,price.price_1,price.saleprice,price.qty,
        price.mean_rate,price.score,price.base_price,price.min_sale_price,price.sale_num, price.level_rate_price,price.avg_predict_basep_price,predict_level_price_rate,
        saleprice_level_price_rate,mean_price_rate,process_price,flag,diff_rate,
        case when product_brand_name='苹果' then b.template_id_52 else b.template_id end as template_rank,
        substring(price.product_level_name,1,1) as level_sub,0 as iter_size_bi,0 as iverse_bi,0 as iter_size,0 as iverse,
        0 as weight_cnt,0 as reverse_cnt
        from price_prediction_level2_rate_price_brand_ljc price 
        left join sku2_level_apple_rank b
        on price.product_level_key=b.level_key 
        where price.date=DATE_FORMAT(date_add(curdate(),interval 1 day),'%Y%m%d')
        and price.product_key in  (42780,39289,79871,61768,39320,43335,37508,37606,43569,87095,26783,76605,22115,34708,34709,37680,32837,29606,96320,
        29607,32872,26001,27988,38329,25273,36095,59956,50183,37664,23857,26268,37378,23375,26098,35246,52379,30385,29705,34504,24799,42861,58585,38884,
        26512,17425,30685,72934,33468,37377,69471,96404,17686,34324,24341,29116,32066,35008,35584,37677,27318,43694,25677,38660,37376,34526,29706,34753,
        25676,26099,25519,23534,27110,14990,27036,28999,35006,75982,63441,55748,38257,32528,36744,47987,35547,24217,36510,29115,10025,66071,34286,23797,
        58472,27035,32632,32835,23535,100642,96407,32776,35271,81728,28525,17304,23281,90534,17869,26780,27297,26467,28829,28466,36291,81856,36493,35181,
        35585,32068,26913,28968,90726,58587,30384,24390,90590,25641,38564,27627,29836,35007,20418,34164,37519,25097,32972,17507,37676,38297,20421,52548,
        22187,58583,27646,68750,21036,35035,27882,23749,23698,52380,18030,23760,9478,81750,25948,26707,26912,69129,17896,69469,35480,38972,39214,84300,
        17258,34044,34403,17493,28566,39020,25681,28684,26201,17486,35135,32006,29001,27405,62758,97279,17733,24966,59542,20127,34356,19533,37094,35623,
        19534,34352,29000,29413,26380,31945,37109,17494,97281,36439,37634,29002,35941,31567,62755,26381,17521,35622,43303,35557,17488,73875,37051,34402,
        34401,43349,21581,29512,23758,50185,25040,27997,32385,36096,17520,22709,17489,17669,28625,28421,62752,17325,33004,9208,24215,23776,17713,42860,
        26613,27334,34948,34574,34590,59959,83906,84630,35322,17746,35349,84648,28168,27551,17320,22440,25820,52552,34686,84662,39141,57426,26490,37596,
        55383,17616,30044,33281,31007,19343,34733,52555,42879,34688,28612,43650,39142,39143,32850,31008,25240,57425,23049,30985,38856,17712,26170,37019,
        17259,38961,29291,42880,17615,92590,26078,27908,62098,25682,17887,32244,43056,38882,27764,26959,23286,17252,69834,27550,34687,35362,26958,35256,
        99452,21769,26422,25717,34734,20742,76903,38600,14972,32634,33280,28760,24240,23460,25239,29693,68759,32019,24239,68768,30602,55534,42881,23668,
        26911,42519,36047,23422,98528,25827,17462,66428,36046,27639,23423,17459,34701,20079,36045,43510,32292,17752,98526,43512,27637,98525,25680,32291,
        17460,17726,32290,43513,27640,36044,25679,2246,43511,2247,17461,17457,17458,17455,98527,26777,37528,92705,39021,34771,65453,65464,63537,28255,
        34468,24793,35548,94040,43397,25238,29458,39029,37557,43360,95833,37283,95936,58513,42958,96533,57844,23764,34896,25616,27931,34897,24349,37512,
        27491,25521,17610,30202,23740,32885,26716,23289,26266,29065,23257,20603,32047,25522,27693,27483,26715,43664,22117,17903,43696,20144,29439,24993,
        25752,17875,23865,17614,37524,15054,27950,25776,27148,25024,24078,26063,27042,22140,80724,42676,69298,38394,26983,35591,20210,17889,20212,25371,
        17955,21055,20698,25431,25777,25778,23275,27861,42701,34755,23125,27859,33436,20658,36246,32089,17578,58309,27344,26493,26536,30174,24552,27783,
        26102,33415,27784,23814,25232,20122,32827,37100,99441,34575,27781,32988,17647,29260,68391,17967,65768,36687,35671,32588,43692,23587,23968,26927,
        96157,28495,29117,55464,26167,37589,17345,33498,39177,26464,17886,25765,35290,30173,25997,36248,76793,36398,15080,33416,29261,42842,76608,17527,
        99440,26491,68782,35125,27643,34756,27782,35106,25806,17549,33012,36101,24348,36247,31445,17321,25432,29246,89018,34397,90758,29937,31767,36897,
        36249,84715,25231,36708,32460,37018,32955,26545,23346,17698,17795,24992,23891,39304,17793,29348,35809,26492,27280,20751,37330,52549,39165,39025,
        38676,24347,28614,35830,32461,43264,34376,17895,25998,26431,36805,27860,34754,43693,34576,30175,23756,91955,31353,35515,43240,37598,26535,34694,
        50184,35582,31550,37650,74097,34448,37703,29135,74807,48128,27991,72658,35049,79977,25935,36300,26769,26540,32974,24996,27501,23613,36394,28480,
        29845,20174,34795,35197,35044,28478,82113,17338,26899,29208,37545,39004,35453,58169,39236,25731,34405,29003,36939,97529,43408,24497,29534,22706,
        35527,76595,34949,33007,27893,29118,28443,27778,37581,27011,25400,17337,43393,27270,23866,33009,32511,35100,34086,57512,31097,87588,73334,29366,
        76898,17611,37217,28121,36250,36610,23862,54482,43409,76437,52406,23906,32471,27549,76598,82104,42646,20092,26662,58152,20499,34598,38696,42595,
        29637,52320,36804,60629,25401,36093,27006,35564,20173,27372,37653,23788,27822,23820,28779,27980,24995,36494,42596,36749,74559,65095,68143,20618,
        32472,33363,36677,25853,20606,37077,37679,27602,27995,19657,28253,76882,26809,31440,34005,37535,26808,20204,33425,37334,27190,34443,29222,26197,
        27301,28165,25985,91803,33194,26513,29519,43407,21885,23077,23619,27541,32243,37255,29815,38664,43615,38619,36160,59145,43614,32633,36384,36908,
        52550,38359,29805,69768,37578,35833,35795,37656,59144,48162,42610,74448,69616,36313,39065,39043,91768,35596,87961,34202,38255,42609,27600,64653,
        34054,32821,26428,34287,59068,38745,39064,34053,81879,32716,35163,66108,35660,34464,22441,25950,32050,68127,17857,17988,76354,29556,77595,37066,
        25303,54809,37487,26129,19665,29500,27512,37604,34431,29807,23614,57423,28502,34730,27253,29777,17657,20143,32890,34096,29666,36628,39001,32022,
        42705,27877,81871,35762,58251,26062,36462,37600,92439,26027,52551,34748,23819,32581,23640,36784,17579,25422,25170,27756,23841,53126,29263,54816,
        27793,47922,94171,38584,81493,17787,29443,36633,27170,59539,26671,17912,35884,19664,24232,17785,34808,26165,62829,35963,37205,36789,39087,20692,
        38630,34097,35164,77316,26476,32884,36352,58631,35005,33500,59536,58300,36985,37687,36692,74184,43220,34365,29023,43695,32055,35700,98349,32054,
        35699,78504,38959,75524,38250,94077,59538,32958,37627,52557,
        37587,35451,35497,30423,94085,34809,43241,98348,57879,56588,57619,76513,26168,27535,26086,
        111413,37330,111365,115120,114989,112846,113325,110293,112285,112648,111336,109022,110593,110295,110288,116018,115792,115287,112856,115658,110125,
        110590,112387,115118,115816,115507,109096,113518,115508,106485,109002,115818,99442,112897,110289,109352,110169,111445,112893,108928,115648,109424,
        110840,115119,109025,113308,115819,79977,111419,43697,107954,113608,36300,112919,112858,115793,107453,109422,116430,110838,98584,116105,109937,115766,
        80724,113488,110294,114969,112694,112766,107811,112112,116068,32974,32007,96296,115278,115817,112764,77234,99955,107154,114992,17875,110591,99884,88860,
        58302,112693,98747,91797,35978,52514,113614,76640,43256,19293,112652,115425,34462,27681,23995,26810,20143,116107,65777,109379,32836,111030
            )
"""
# AND price.product_level_name !='A2' and price.product_sku_key in (772111)
# price.product_key in  (27877, 22441, 26428, 20692, 35963, 34748, 35164, 34097, 35884, 34053, 33009, 23866, 28121, 23788, 34086, 23906, 35100, 27822, 22706, 29637, 33498, 34575, 25806, 17527, 30173, 35290, 27859, 34755, 30175, 29261, 32291, 36046, 34687, 26613, 26422, 31008, 25820, 32850, 29693, 27551, 29291, 28760, 29116, 32835, 24217, 25519, 34753, 24799, 24390, 17425, 32066, 26467)
# and price.product_key in  (33012) and price.product_sku_key in  (2694100)
#(27877, 22441, 26428, 20692, 35963, 34748, 35164, 34097, 35884, 34053, 33009, 23866, 28121, 23788, 34086, 23906, 35100, 27822, 22706, 29637, 33498, 34575, 25806, 17527, 30173, 35290, 27859, 34755, 30175, 29261, 32291, 36046, 34687, 26613, 26422, 31008, 25820, 32850, 29693, 27551, 29291, 28760, 29116, 32835, 24217, 25519, 34753, 24799, 24390, 17425, 32066, 26467)

def inverse_rate_fun(group):
    group = group[1].sort_values(by=['template_rank','product_level_key','mean_rate'], ascending=[True,True,False])
    group.reset_index(drop=True, inplace=True)
    group['product_level_name_tmp'] = group['product_level_name']
    group['rank_tmp'] = group['rank']
    group['saleprice_tmp'] = group['saleprice']

    size = group.shape[0]
    result_t = group
    if size <= 2:
        return result_t


    for index in range(size):

        iter_size = 0
        iter_size_bi = 0
        iverse = 0
        iverse_bi = 0
        if result_t.loc[index, 'level_sub'] in ['S', 'A', 'B', 'C', 'D'] and index + 1 <= size:
            for i in range(index + 1, size, 1):
                if result_t.loc[index, 'template_rank'] == result_t.loc[i, 'template_rank']:
                    continue
                iter_size += 1
                iter_size_bi += 1
                if result_t.loc[index, 'process_price'] < result_t.loc[i, 'process_price'] and result_t.loc[
                    index, 'template_rank'] < result_t.loc[i, 'template_rank']:
                    iverse_bi = iverse_bi + 1
                    result_t.loc[i, 'weight_cnt'] = result_t.loc[i, 'weight_cnt'] + 1
                    result_t.loc[index, 'weight_cnt'] = result_t.loc[index, 'weight_cnt'] + 1

                if result_t.loc[index, 'saleprice'] < result_t.loc[i, 'saleprice'] and result_t.loc[
                    index, 'template_rank'] < result_t.loc[i, 'template_rank']:
                    iverse = iverse + 1

            result_t.loc[index, 'iter_size'] = iter_size
            result_t.loc[index, 'iter_size_bi'] = iter_size_bi
            result_t.loc[index, 'iverse'] = iverse
            result_t.loc[index, 'iverse_bi'] = iverse_bi
        # resDf = resDf.append(result_t.copy())
    return result_t


def inverse_rate_process(result_t):
    t1 = time.time()
    result_t=result_t.loc[result_t.level_sub.isin(['S','A','B','C','D','E'])]
    resDf = pd.DataFrame(columns=result_t.columns.tolist())
    grouped = result_t.groupby('product_sku_key')

    total_gp = grouped.ngroups
    iter=0
    if total_gp>5:
        with Pool(16) as executor:
            for number, result_t in zip(grouped,
                                      executor.map(inverse_rate_fun,
                                                   grouped)):
                resDf = resDf.append(result_t.copy())
                iter += 1
                logger.info('total_gp = {} ,iter={} skuid={} is prime:'.format(total_gp, iter, number[0]))
        logger.info(' inverse_rate_process end use time @{}'.format((time.time() - t1) / 60))
    else:
        for group in grouped:
            result_t = inverse_rate_fun(group)
            resDf = resDf.append(result_t.copy())
    resDf = resDf.drop_duplicates()
    return resDf

def inverse_level_fun(group):
    group = group[1]
    group = group.sort_values(by=['template_rank','product_level_key','mean_rate'], ascending=[True,True,False])
    group.reset_index(drop=True, inplace=True)
    size = group.shape[0]
    product_sku_key=group.loc[0, 'product_sku_key']
    try:
        logger.info("sku_key={}".format(product_sku_key))
        if product_sku_key in (6140579,6140273,6140573,6140579):
            logger.info('dss')
        # result_t[['product_level_name', 'template_rank', 'reverse_cnt','weight_cnt', 'process_price', 'process_price_inverse_begin','predict_origin', 'level_rate_price','price_0_7', 'sale_num_0_7', 'iverse_bi_begin', 'thisprice','count']]
        result_t = group
        result_t[['reverse_cnt', 'weight_cnt', 'mean_rate', 'base_price', 'first_base_level_price', 'first_template_rank',
                  'second_base_level_price', 'second_template_rank', 'mean_rate_get_price_diff_rate', 'mean_rate_get_price',
                  'process_price', 'process_price_inverse_begin',
                  'predict_origin', 'level_rate_price', 'thisprice', 'count', 'price_0_7', 'sale_num_0_7', 'price_8_14',
                  'sale_num_8_14', 'product_level_name', 'template_rank', 'iverse_bi_begin']]
        if size <= 2:
            return group

        for i in range(1,size-1):
            # logger.info('weight_price index={}'.format(i))
            if i == size - 1:
                continue
            if result_t.loc[i,'mean_rate_get_price_diff_rate']>0.05 and result_t.loc[i,'sale_num_0_7']<3 and result_t.loc[i,'level_sub'] in ['S','A','B','C','D','E'] \
                    and result_t.loc[i-1,'process_price']>result_t.loc[i,'process_price'] \
                    and result_t.loc[i, 'thisprice']>0:
                thisprice = result_t.loc[i, 'thisprice']
                price_0_7 = result_t.loc[i, 'price_0_7']
                before_price = result_t.loc[i-1, 'process_price']

                next_price = result_t.loc[i+1, 'process_price']
                final_price=result_t.loc[i, 'process_price']
                if price_0_7>0 :
                    final_price = max(min(price_0_7,before_price*0.99),next_price*1.01)
                elif thisprice>0 and final_price<thisprice:
                    final_price = max(min(thisprice, before_price * 0.99), next_price * 1.01)
                result_t.loc[i,'process_price']=final_price

        for index in range(1, size * 2, 1):

            result_t['weight_cnt'] = 0
            result_t = inverse_rate_process(result_t)
            max_index = result_t.loc[result_t.weight_cnt == result_t.weight_cnt.max()]
            max_index = max_index.sort_values(by=['reverse_cnt', 'template_rank'], ascending=[True, False])
            reverse_index = max_index.index[0]
            middle_price = result_t.loc[reverse_index, 'process_price']
            thisprice = result_t.loc[reverse_index, 'thisprice']
            if result_t.loc[reverse_index, 'weight_cnt'] == 0:
                break
            if reverse_index == 0:
                result_t.loc[reverse_index, 'reverse_cnt'] += 1
                if thisprice > 0:
                    if ~np.isnan(thisprice) and middle_price < thisprice:
                        result_t.loc[reverse_index, 'process_price'] = thisprice
                    else:
                        result_t.loc[reverse_index, 'process_price'] = result_t.loc[
                                                                           reverse_index + 1, 'process_price'] * 1.02
                else:
                    result_t.loc[reverse_index, 'process_price'] = result_t.loc[
                                                                       reverse_index + 1, 'process_price'] * 1.02
                continue

            if reverse_index + 1 < size:
                if result_t.loc[reverse_index, 'reverse_cnt'] >= 5:
                    first_index = reverse_index - 1
                    for first_index in range(reverse_index - 1, 0, -1):
                        if result_t.loc[first_index, 'weight_cnt'] == 0 and result_t.loc[
                            first_index, 'template_rank'] != \
                                result_t.loc[reverse_index, 'template_rank']:
                            break
                    for next_index in range(reverse_index + 1, size, 1):
                        if result_t.loc[next_index, 'weight_cnt'] == 0 and result_t.loc[next_index, 'template_rank'] != \
                                result_t.loc[reverse_index, 'template_rank'] and result_t.loc[
                            first_index, 'process_price'] > result_t.loc[next_index, 'process_price']:
                            break
                    bulk_price = (result_t.loc[first_index, 'process_price'] - result_t.loc[
                        next_index, 'process_price']) / (
                                         next_index - first_index)
                    # begin_after_avg_price = result_t.loc[first_index, 'process_price'] - bulk_price * (
                    #             reverse_index - first_index)
                    for reverse in range(first_index + 1, next_index, 1):
                        if result_t.loc[reverse, 'weight_cnt'] > 0:
                            result_t.loc[reverse, 'reverse_cnt'] += 1
                            result_t.loc[reverse, 'process_price'] = result_t.loc[
                                                                         first_index, 'process_price'] - bulk_price * (
                                                                             reverse - first_index)
                    continue
                next_price = result_t.loc[reverse_index + 1, 'process_price']
                upper_price = result_t.loc[reverse_index - 1, 'process_price']
                begin_after_avg_price = (result_t.loc[reverse_index - 1, 'process_price'] + result_t.loc[
                    reverse_index + 1, 'process_price']) / 2

                # 针对当前行的价格比下面的要小，确定是否调整当前行的价格 或者下面一行的价格
                if reverse_index + 1 < size and middle_price < result_t.loc[reverse_index + 1, 'process_price']:
                    reverse_up_rate = abs(
                        result_t.loc[reverse_index - 1, 'process_price'] - result_t.loc[reverse_index, 'process_price']) / \
                                      result_t.loc[reverse_index, 'process_price']
                    reverse_down_rate = abs(
                        result_t.loc[reverse_index + 1, 'process_price'] - result_t.loc[reverse_index, 'process_price']) / \
                                        result_t.loc[reverse_index, 'process_price']

                    if result_t.loc[reverse_index, 'sale_num_0_7'] > result_t.loc[reverse_index + 1, 'sale_num_0_7'] \
                            or result_t.loc[reverse_index, 'count'] > result_t.loc[
                        reverse_index + 1, 'count']:  # 此种针对下面一行的出货量少 ，需要调整下面的价格
                        if result_t.loc[reverse_index + 1, 'price_0_7'] > 0 or result_t.loc[
                            reverse_index + 1, 'thisprice'] > 0:
                            next_bids_price = min(result_t.loc[reverse_index + 1, 'process_price'],
                                                  result_t.loc[reverse_index + 1, 'process_price'] \
                                                      if result_t.loc[reverse_index + 1, 'price_0_7'] <= 0 else
                                                  result_t.loc[reverse_index + 1, 'price_0_7'])

                            next_bids_price = min(next_bids_price,
                                                  next_bids_price if result_t.loc[reverse_index + 1, 'thisprice'] <= 0 else
                                                  result_t.loc[reverse_index + 1, 'thisprice'])

                            if reverse_index + 2 < size:
                                if result_t.loc[reverse_index + 1, 'process_price'] > result_t.loc[
                                    reverse_index + 2, 'process_price'] and result_t.loc[
                                    reverse_index, 'reverse_cnt'] <= 5:  # 当前行不倒挂，则希望调低此行价格，继续保持不到挂
                                    if next_bids_price > result_t.loc[reverse_index + 2, 'process_price']:
                                        result_t.loc[reverse_index + 1, 'process_price'] = next_bids_price
                                    else:
                                        result_t.loc[reverse_index + 1, 'process_price'] = min(
                                            result_t.loc[reverse_index + 2, 'process_price'] + 1,
                                            result_t.loc[reverse_index - 1, 'process_price'])
                                    result_t.loc[reverse_index + 1, 'reverse_cnt'] += 1
                                    continue
                    elif reverse_up_rate > 0.05 and reverse_down_rate > 0.05:    #针对当前价格：比上下价格都明显差距c
                        reverse_thisprice = result_t.loc[reverse_index - 1, 'process_price'] * 0.95 if result_t.loc[
                                                                                                           reverse_index, 'thisprice'] <= 0 else \
                            result_t.loc[reverse_index, 'thisprice']
                        reverse_price = max(result_t.loc[reverse_index - 1, 'process_price'] * 0.95, reverse_thisprice)
                        if result_t.loc[reverse_index, 'process_price'] > reverse_price:
                            result_t.loc[reverse_index, 'process_price'] = reverse_price + (
                                    result_t.loc[reverse_index - 1, 'process_price'] - reverse_price) * 0.5
                        else:
                            result_t.loc[reverse_index, 'process_price'] = reverse_price
                        result_t.loc[reverse_index, 'reverse_cnt'] += 1
                        continue

                        # 1、middle_price!=begin_after_avg_price：防止重复更新
                if result_t.loc[
                    reverse_index - 1, 'process_price'] >= begin_after_avg_price and middle_price != begin_after_avg_price and (
                        ~np.isnan(
                            thisprice) and thisprice > 0 and middle_price != thisprice) and middle_price != next_price * 1.03:
                    diff_rate = begin_after_avg_price / thisprice - 1
                    if np.isnan(result_t.loc[reverse_index, 'thisprice']) or (diff_rate > -0.06 and diff_rate <= 0.06):
                        result_t.loc[reverse_index, 'process_price'] = begin_after_avg_price
                        # continue
                    elif (reverse_index+1)<size and   result_t.loc[reverse_index+1, 'thisprice'] >0:
                        result_t.loc[reverse_index, 'process_price'] = min(begin_after_avg_price,result_t.loc[reverse_index+1, 'process_price']*1.01)
                    else:
                        if result_t.loc[reverse_index - 1, 'thisprice'] > 0:
                            result_t.loc[reverse_index, 'process_price'] = upper_price - 1
                        else:
                            if diff_rate > 0:
                                result_t.loc[reverse_index, 'process_price'] = min(thisprice * 1.06, upper_price - 1)
                            else:
                                result_t.loc[reverse_index, 'process_price'] = min(thisprice * 0.94, upper_price - 1)
                        # continue
                    result_t.loc[reverse_index, 'reverse_cnt'] += 1
                    continue


                else:
                    diff_price = result_t.loc[reverse_index - 1, 'process_price'] - middle_price
                    diff_rate = diff_price / result_t.loc[reverse_index - 1, 'process_price']
                    if diff_price > 0 and diff_rate > 0.01:
                        result_t.loc[reverse_index, 'process_price'] = middle_price + diff_price * 0.2
                    else:
                        first_index = reverse_index - 1
                        for first_index in range(reverse_index - 1, 0, -1):
                            if result_t.loc[first_index, 'weight_cnt'] == 0 and result_t.loc[
                                first_index, 'template_rank'] != result_t.loc[reverse_index, 'template_rank']:
                                break
                        for next_index in range(reverse_index + 1, size, 1):
                            if result_t.loc[next_index, 'weight_cnt'] == 0 and result_t.loc[next_index, 'template_rank'] != \
                                    result_t.loc[reverse_index, 'template_rank'] and result_t.loc[
                                first_index, 'process_price'] > result_t.loc[next_index, 'process_price']:
                                break

                        for reverse in range(first_index + 1, next_index - 1, 1):
                            # tmp=result_t[['template_rank', 'mean_rate', 'product_level_key', 'product_level_name',
                            #           'forecast_reference_price', 'process_price', 'price_0_7','sale_num_0_7', 'weight_cnt']]
                            if result_t.loc[reverse, 'weight_cnt'] > 0:
                                if reverse > 0 and (result_t.loc[reverse, 'process_price'] <= result_t.loc[
                                    reverse - 1, 'process_price'] or result_t.loc[reverse, 'template_rank'] == result_t.loc[
                                                        reverse - 1, 'template_rank']):
                                    continue
                                    # pass
                                else:
                                    if reverse > 0 and result_t.loc[reverse, 'process_price'] > result_t.loc[
                                        reverse - 1, 'process_price'] and result_t.loc[reverse, 'template_rank'] != \
                                            result_t.loc[reverse - 1, 'template_rank']:
                                        if result_t.loc[reverse, 'sale_num_0_7'] <= result_t.loc[
                                            reverse - 1, 'sale_num_0_7']:
                                            result_t.loc[reverse, 'process_price'] = result_t.loc[
                                                                                         reverse - 1, 'process_price'] - 1
                                            result_t.loc[reverse, 'reverse_cnt'] += 1
                                            for next_equil_rank in range(reverse + 1, next_index - 1, 1):
                                                if result_t.loc[reverse, 'template_rank'] == result_t.loc[
                                                    next_equil_rank, 'template_rank']:
                                                    result_t.loc[next_equil_rank, 'process_price'] = result_t.loc[
                                                                                                         reverse - 1, 'process_price'] - 1
                                                else:
                                                    break

                                        else:
                                            if reverse > 1 and result_t.loc[reverse, 'process_price'] < result_t.loc[
                                                reverse - 2, 'process_price']:
                                                result_t.loc[reverse - 1, 'process_price'] = result_t.loc[
                                                                                                 reverse, 'process_price'] + 1
                                                result_t.loc[reverse - 1, 'reverse_cnt'] += 1
                                            elif reverse > 1 and result_t.loc[reverse, 'process_price'] > result_t.loc[
                                                reverse - 2, 'process_price']:
                                                result_t.loc[reverse - 1, 'process_price'] = result_t.loc[
                                                                                                 reverse - 2, 'process_price'] - 1
                                                result_t.loc[reverse, 'process_price'] = result_t.loc[
                                                                                             reverse - 1, 'process_price'] - 1
                                                result_t.loc[reverse - 1, 'reverse_cnt'] += 1
                                                result_t.loc[reverse, 'reverse_cnt'] += 1
                                                logger.info('特殊情况=sku={}'.format(result_t.loc[0, 'product_sku_key']))

            else:
                result_t.loc[reverse_index, 'process_price'] = result_t.loc[reverse_index - 1, 'process_price'] * 0.99

            result_t.loc[reverse_index, 'reverse_cnt'] += 1
            logger.info('product_sku_key={},iter={}'.format(product_sku_key,index))

    except Exception as e:
        logger.info('product_sku_key={},error {}'.format(product_sku_key,e))

    result_t['process_price_tmp'] = result_t['process_price']
    result_t['product_level_name_tmp'] = result_t['product_level_name']
    return result_t

def inverse_level_process(input_df):
    t1 = time.time()
    input_df['process_price_inverse_begin']=input_df['process_price'].copy(deep=True)
    input_df['process_price_level_reverse1'] = input_df['process_price']
    result_t=input_df.sort_values(by=['template_rank', 'mean_rate'],ascending=[True, False])
    # result_t=result_t.loc[result_t.product_sku_key==187906]

    leve_first_df,leve_second_df = first_second_base_fun(result_t)
    result_t['product_sku_key'] = result_t['product_sku_key'].astype(np.int)
    result_t = result_t.merge(leve_first_df,how='left',on=['product_sku_key'])
    result_t = result_t.merge(leve_second_df,how='left',on=['product_sku_key'])
    result_t=result_t.fillna(0)
    result_t.loc[result_t.second_base_level_price == 0, 'second_base_level_price'] = result_t.loc[
        result_t.second_base_level_price == 0, 'first_base_level_price']
    result_t['first_lit_second_flag']=0
    result_t['avg_base_level_price'] = (result_t['first_base_level_price']+result_t['second_base_level_price'])/2
    result_t.loc[result_t.first_base_level_price <result_t.second_base_level_price, 'first_base_level_price']=result_t.loc[result_t.first_base_level_price <result_t.second_base_level_price, 'avg_base_level_price']
    result_t.loc[result_t.first_base_level_price <result_t.second_base_level_price, 'second_base_level_price']=result_t.loc[result_t.first_base_level_price <result_t.second_base_level_price, 'avg_base_level_price']

    weight_cnt_df = result_t.loc[result_t.weight_cnt > 0]
    weight_cnt_df['mean_rate_get_price'] = weight_cnt_df['process_price']
    weight_cnt_df.loc[weight_cnt_df.template_rank <= weight_cnt_df.first_template_rank, 'mean_rate_get_price'] = \
    weight_cnt_df.loc[weight_cnt_df.template_rank <= weight_cnt_df.first_template_rank, 'first_base_level_price'] * \
    weight_cnt_df.loc[weight_cnt_df.template_rank <= weight_cnt_df.first_template_rank, 'mean_rate']

    weight_cnt_df.loc[weight_cnt_df.template_rank >= weight_cnt_df.second_template_rank, 'mean_rate_get_price'] = \
    weight_cnt_df.loc[weight_cnt_df.template_rank >= weight_cnt_df.first_template_rank, 'second_base_level_price'] * \
    weight_cnt_df.loc[weight_cnt_df.template_rank >= weight_cnt_df.second_template_rank, 'mean_rate']

    weight_cnt_df.loc[(weight_cnt_df.template_rank > weight_cnt_df.first_template_rank) & (
                weight_cnt_df.template_rank < weight_cnt_df.second_template_rank), 'mean_rate_get_price'] = \
    weight_cnt_df.loc[(weight_cnt_df.template_rank > weight_cnt_df.first_template_rank) & (
                weight_cnt_df.template_rank < weight_cnt_df.second_template_rank),
                      'avg_base_level_price'] * weight_cnt_df.loc[
        (weight_cnt_df.template_rank > weight_cnt_df.first_template_rank) & (
                    weight_cnt_df.template_rank < weight_cnt_df.second_template_rank), 'mean_rate']

    weight_cnt_df['mean_rate_get_price_diff_rate'] = abs(weight_cnt_df['process_price']/ weight_cnt_df['mean_rate_get_price']-1)

    result_t = result_t.merge(weight_cnt_df[['product_sku_key', 'product_level_key', 'mean_rate_get_price','mean_rate_get_price_diff_rate']],
                              how='left', on=['product_sku_key', 'product_level_key'])
    result_t = result_t.fillna(0)
    result_t.loc[(result_t.level_sub.isin(['S','A','B','C','D','E']))&(result_t.sale_num_0_7<3)&(result_t.mean_rate_get_price_diff_rate>=0.10),'process_price' ] = result_t.loc[(result_t.level_sub.isin(['S','A','B','C','D','E']))&(result_t.sale_num_0_7<3)&(result_t.mean_rate_get_price_diff_rate>=0.10),'mean_rate_get_price']
    # weight_cnt_df.loc[ weight_cnt_df.product_sku_key == 2550734, ['template_rank','product_level_key','product_level_name','mean_rate', 'process_price', 'mean_rate_get_price', 'price_0_7','sale_num_0_7', 'first_level_rate', 'first_base_level_price','second_level_rate', 'second_base_level_price', 'avg_base_level_price']].sort_values(by=['template_rank','product_level_key'])
    # result_t=result_t.loc[result_t.product_sku_key==2550734]
    resDf = pd.DataFrame(columns=result_t.columns.tolist())
    grouped = result_t.groupby('product_sku_key')
    total_gp=grouped.ngroups
    iter=0
    if total_gp>5:
        with Pool(16) as executor:
            for number, result_t in zip(grouped,
                                      executor.map(inverse_level_fun,
                                                   grouped)):
                resDf = resDf.append(result_t.copy())
                iter += 1
                logger.info('total_gp ={},iter={} skuid={} is prime:'.format(total_gp,iter, number[0]))
        logger.info(' inverse_level_process end use time @{}'.format((time.time() - t1) / 60))
    else:
        for group in grouped:
            result_t = inverse_level_fun(group)
            resDf = resDf.append(result_t.copy())
    return resDf

def first_second_base_fun(df444):

    leve_first_second_df= df444.copy()
    leve_first_second_df['count_rank'] = leve_first_second_df['count'].groupby(leve_first_second_df['product_sku_key']).rank(
        ascending=False, method='first')
    leve_first_second_df['count'].groupby(leve_first_second_df['product_sku_key']).rank(ascending=False, method='first')

    leve_first_second_df = leve_first_second_df.loc[leve_first_second_df.count_rank.isin([1,2])]
    leve_first_second_df.loc[leve_first_second_df.sale_num_0_7>=5,'thisprice']=leve_first_second_df.loc[leve_first_second_df.sale_num_0_7>=5,'price_0_7']
    leve_first_second_df['rank_level'] = 1000-leve_first_second_df['template_rank']+100-leve_first_second_df['product_level_key']
    leve_first_second_df['product_sku_key'] = leve_first_second_df['product_sku_key'].astype(np.int)
    leve_first_second_df['rank_level'] = leve_first_second_df['rank_level'].astype(np.int)
    leve_first_second_df['count_rank'] = leve_first_second_df[['product_sku_key','rank_level']].groupby(['product_sku_key'])[['rank_level']].rank(ascending=False, method='first')
    leve_first_second_df.loc[leve_first_second_df['count']<=0,'thisprice']=leve_first_second_df.loc[leve_first_second_df['count']<=0,'process_price']
    leve_first_second_df['base_level_price']=leve_first_second_df['thisprice']/leve_first_second_df['mean_rate']
    # if leve_first_second_df.shape[0]>2:
    #     leve_first_second_df = leve_first_second_df.head(2)
    leve_first_second_df.reset_index(inplace=True)
    # leve_first_df = leve_first_second_df.iloc[0]
    # leve_second_df = leve_first_second_df.iloc[1]
    # leve_first_second_df[['product_level_key','iverse_bi_begin']].rename(columns={'iverse_bi_begin':'iverse_bi_begin22'})
    leve_first_df=leve_first_second_df.loc[leve_first_second_df.count_rank==1,['product_sku_key','mean_rate','base_level_price','template_rank','product_level_name']].rename(columns={"mean_rate": "first_level_rate", 'base_level_price': 'first_base_level_price','template_rank':'first_template_rank','product_level_name':'first_level_name'})
    leve_second_df=leve_first_second_df.loc[leve_first_second_df.count_rank==2,['product_sku_key','mean_rate','base_level_price','template_rank','product_level_name']].rename(columns={"mean_rate": "second_level_rate", 'base_level_price': 'second_base_level_price','template_rank':'second_template_rank','product_level_name':'second_level_name'})

    return leve_first_df,leve_second_df

def fetch_process_data(flag=True):

    if flag:
        #数据库取数逻辑处理
        result_t=mysql_prediction_processor.load_sql(query_sql)

        #获取skulevel，按照三个七天进行价格段的处理
        td=format_date_string(get_today())
        period_data=get_period_price_fun(td,flag=True)
        period_data=period_data.loc[period_data.thisprice>0]

        #价格段的数据合并到数据中
        result_t=result_t.merge(period_data,how='left',on=['product_sku_key','product_level_key'])
        result_t=result_t.fillna(0)
        check_conflict_file(FILE_DIR,'sku2_price_fetch_process_data_ljc.pkl',is_remove_old_file=True)
        save_pickle_data(FILE_DIR+'sku2_price_fetch_process_data_ljc.pkl', result_t)
    else:
        result_t = load_pickle_data(FILE_DIR+'sku2_price_fetch_process_data_ljc.pkl')
    return result_t



def reverse_inverse_data(resDf):

    #倒挂修复前的数据进行备份对照
    resDf['iter_size_begin']=resDf['iter_size']
    resDf['iter_size_bi_begin']=resDf['iter_size_bi']
    resDf['iverse_begin']=resDf['iverse']
    resDf['iverse_bi_begin']=resDf['iverse_bi']
    resDf = inverse_rate_process(resDf)
    #数据进行倒挂修复
    resDf=inverse_level_process(resDf)

    #倒挂处理完的数据进行重新倒挂处理
    resDf=inverse_rate_process(resDf)
    # resDf[['rank', 'reverse_cnt', 'process_price', 'process_price_inverse_begin', 'process_price_level_reverse1','price_0_3', 'price_0_7', 'predict_origin', 'iverse_bi_begin', 'product_level_name']]
    resDf.drop_duplicates(subset=['product_sku_key', 'product_level_key'], keep='first', inplace=True)

    #数据保存，应用于下次的属性倒挂处理
    check_conflict_file(FILE_DIR, 'sku2_price_level_reverse_process1_data_ljc.pkl')
    save_pickle_data(FILE_DIR+'sku2_price_level_reverse_process1_data_ljc.pkl', resDf)
    return resDf

def process1_level_inverse_fun():

    result_t = fetch_process_data(flag=True)
    # result_t = fetch_process_data(flag=False)
    # result_t=result_t.loc[(result_t.product_brand_name.isin(['三星1','一加1','苹果'])) & (result_t.product_sku_key.isin([772109])) ]
    # result_t=result_t.loc[(result_t.product_key.isin([43511]))]
    # result_t=result_t.loc[(result_t.product_sku_key.isin([6140579,17853306]))]
    # 进行倒挂测算
    # resDf = inverse_rate_process(result_t)
    # resDf = inverse_rate_process(resDf)
    resDf=result_t

    # 数据进行倒挂修复
    res_df = reverse_inverse_data(resDf)
    return res_df

def main():
    process1_level_inverse_fun()

if __name__ == '__main__':
    main()

